﻿Imports System.Data.OleDb
Imports DTO

Public Class ThongKeDAO
    Public Sub DeleteDuLieu()
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim strSQL As String = "DELETE FROM ThongKe"
        Dim comand As New OleDbCommand(strSQL, cnn)
        comand.ExecuteNonQuery()
        cnn.Close()
    End Sub

    Public Sub InsertDuLieu(ByVal tkDTO As ThongKeDTO)
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim str = "Insert Into ThongKe(MaLoaiTietKiem,TenLoaiTietKiem,TongThu,TongChi)values(?,?,?,?)"
        Dim para As OleDbParameter
        Dim comand As New OleDbCommand(str, cnn)
        para = comand.Parameters.Add("@MaLoaiTietKiem", OleDbType.Integer)
        para.Value = tkDTO.MaLoaiTietKiem

        para = comand.Parameters.Add("@TenLoaiTietKiem", OleDbType.WChar)
        para.Value = tkDTO.TenLoaiTietKiem

        para = comand.Parameters.Add("@TongThu", OleDbType.Integer)
        para.Value = tkDTO.TongThu

        para = comand.Parameters.Add("@ThongChi", OleDbType.Integer)
        para.Value = tkDTO.TongChi

        comand.ExecuteNonQuery()
    End Sub

    Public Function LayTongChi(ByVal _date As Date) As DataTable
        Dim dt As New DataTable()
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim str = "SELECT tk.MaLoaiTietKiem,Sum(pr.SoTienRut) FROM PhieuRutTien pr,SoTietKiem tk WHERE Month(pr.NgayRut) = " + _date.Month.ToString() + " And Year(pr.NgayRut) = " + _date.Year.ToString()
        str += " And tk.MaSo = pr.MaSo"
        str += " Group By tk.MaLoaiTietKiem "
        Dim comand As New OleDbCommand(str, cnn)
        Dim Adapter As New OleDbDataAdapter(comand)
        Adapter.Fill(dt)
        Return dt
    End Function

    Public Sub CapNhatDuLieu(ByVal ma As String, ByVal num As Long)
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim sql = "Update ThongKe set TongChi = 45000000" + " Where MaLoaiTietKiem = " + ma.ToString()
        Dim comand As New OleDbCommand(sql, cnn)
        comand.ExecuteNonQuery()
    End Sub
End Class
